# -*- coding: utf-8 -*-  
import sqlite3
from sqlite3 import Error
from datetime import datetime, timedelta

class SQLiteHelper:
    def __init__(self, db_name):
        self.db_name = db_name
        self.conn = None
        self.create_connection()
 
    def create_connection(self):
        """创建数据库连接"""
        try:
            self.conn = sqlite3.connect(self.db_name)
            #print("Connected to")
        except Error as e:
            print(e)
 
    def create_table(self, create_table_sql):
        """创建表"""
        try:
            c = self.conn.cursor()
            c.execute(create_table_sql)
            self.conn.commit()
        except Error as e:
            print(e)
 
    def execute_query(self, query, params=()):
        """执行查询"""
        try:
            cur = self.conn.cursor()
            cur.execute(query, params)
            self.conn.commit()
            return cur.fetchall()
        except Error as e:
            print(e)
            return None
 
    def execute_non_query(self, query, params=()):
        """执行非查询操作（如INSERT, UPDATE, DELETE）"""
        try:
            cur = self.conn.cursor()
            cur.execute(query, params)
            self.conn.commit()
        except Error as e:
            print(e)
            self.conn.rollback()
            return None
        return cur.lastrowid  # 返回插入行的ID，如果适用的话
 
    def close_connection(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            #print("Connection closed.")

if __name__ == '__main__':
    db_helper = SQLiteHelper('example.db')
    
    create_company_table_sql = '''CREATE TABLE IF NOT EXISTS t_company (id INTEGER PRIMARY KEY, target INTEGER, create_time TIMESTAMP)'''
    create_shop_table_sql = '''CREATE TABLE IF NOT EXISTS t_shop (id INTEGER PRIMARY KEY, name CHAR(50), create_time TIMESTAMP)'''
    create_sale_table_sql = '''CREATE TABLE IF NOT EXISTS t_sale (id INTEGER PRIMARY KEY, name CHAR(50), day CHAR(50), amount INTEGER, create_time TIMESTAMP)'''
    '''
    today = datetime.now()
    db_helper.create_table(create_shop_table_sql)
    insert_sql = 'INSERT INTO t_shop (name, create_time) VALUES (?, ?)'
    db_helper.execute_non_query(insert_sql, ('安踏大店', today))
    db_helper.execute_non_query(insert_sql, ('安踏二店', today))
    db_helper.execute_non_query(insert_sql, ('安踏儿童店',today))
    db_helper.execute_non_query(insert_sql, ('海澜之家', today))
    db_helper.execute_non_query(insert_sql, ('蜘蛛王一店', today))
    db_helper.execute_non_query(insert_sql, ('蜘蛛王二店', today))
    db_helper.execute_non_query(insert_sql, ('耐克', today))
    db_helper.execute_non_query(insert_sql, ('阿迪达斯', today))

    db_helper.create_table(create_company_table_sql)
    insert_sql = 'INSERT INTO t_company (target, create_time) VALUES (?, ?)'
    db_helper.execute_non_query(insert_sql, (5000000, today))
    '''
    db_helper.create_table(create_sale_table_sql)
    '''
    db_helper.create_table(create_sale_table_sql)
    total_amount_sql = "SELECT sum(amount) FROM t_sale"
    total_amount = db_helper.execute_query(total_amount_sql)
    print(total_amount[0][0])

    day_amount_sql = "SELECT sum(amount) FROM t_sale where create_time < " + "'2025-07-24'"
    day_amount = db_helper.execute_query(day_amount_sql)
    print(day_amount[0][0])
    store_amount_sql = "SELECT name,sum(amount) FROM t_sale group by name"
    store_amount = db_helper.execute_query(store_amount_sql)
    for item in store_amount:
        print(item[0])
        print(item[1])
    '''
